import pymysql
import json
json_path='libs/db.json'

class DBManager():

    def __init__(self):
        settings_path=open(json_path, "rb")
        obj=json.load(settings_path)
        self.conn=pymysql.connect(
            host=obj['mysql']['ip'],
            user=obj['mysql']['usr'],
            passwd=obj['mysql']['pwd'],
            db=obj['mysql']['db'],
            charset='utf8'
            )
        self.db=self.conn.cursor()
    
    def setup(self):
        try:
            sql="create table if not exists images\
            (name varchar(50) primary key,\
            picture longblob,\
            point longblob,\
            line longblob);"

            self.db.execute(sql)
            self.conn.commit()
            print("here")
            
        except Exception as e:
            print(e)

    def insert(self,name,img,point=[],line=[]):
        print('insert')
        try:
            sql="insert into images(\
                name,\
                picture,\
                point,\
                line) \
                values(%s,%s,%s,%s);"
            self.db.execute(sql,(name,img,json.dumps(point),json.dumps(line)))
            self.conn.commit()
            print("here")
            return True
        except Exception as e:
            print(e)
            return False
    

    def delete_all(self,name):
        print('delete_all')
        try:
            sql="delete from images where name=%s;"
            self.db.execute(sql,(name))
            self.conn.commit()
        except Exception as e:
            print(e)
            return False
    '''
     [

         {"key":0,"point":[x,y]},
         {"key":1,"point":[x,y]},
     ]

    '''
    def select_point(self,name):
        try:
            sql="select point from images where \
            name=%s order by name desc limit 1;"
            self.db.execute(sql,name)
            point=self.db.fetchone()[0]
            return json.loads(point)
        except Exception as e:
            print(e)
            return None
    
    '''
      point:[x,y]
    '''
    def insert_point(self,name,point):
        print('insert_point')
        try:
            sql="select point from images where name=%s order by name desc limit 1;"
            self.db.execute(sql,name)
            key_points=self.db.fetchone()[0]
            #print(key_points)
            key_points=json.loads(key_points)
            n=len(key_points)
            key_points.append({'key':n,'point':point})

            sql="update images set point=%s where name=%s;"
            self.db.execute(sql,(json.dumps(key_points),name))
            self.conn.commit()

            return True

        except Exception as e:
            print(e)
            return False
    def update_point(self,name,key,point):
        print("update_point")
        try:
            sql="select point from images where name=%s order by name desc limit 1;"
            self.db.execute(sql,name)
            #print('5')
            key_points=self.db.fetchone()[0]
            #print('4')
            key_points=json.loads(key_points)
            #print('3')
            key_points_obj=[]
            find_point_status=False
            for i in range(0,len(key_points)):
                if key_points[i]["key"]!=key:
                    key_points_obj.append(key_points[i])
                else:
                    key_points_obj.append({'key':key,'point':point})
            #print('2')
            #print(key_points_obj)
            sql="update images set point=%s where name=%s"
            self.db.execute(sql,(json.dumps(key_points_obj),name))
            #print('1')
            self.conn.commit()

        except Exception as e:
            print(e)
            return False

    def delete_all_point(self,name):
        print('delete_all_point')
        try:
            sql="update images set point=%s where name=%s;"
            self.db.execute(sql,(json.dumps([]),name))
            self.conn.commit()
            return True
        except Exception as e:
            print(e)
            return False
    '''

    '''

    def delete_line(self,name,key):#删除线 点不删 索引不需要变
        print('delete_line')
        try:
            sql="select line from images where name=%s order by name desc limit 1;"
            self.db.execute(sql,name)
            line=self.db.fetchone()[0]
            line=json.loads(line)
            line_obj=[]

            for i in range(0,len(line)):
                is_need_del=False
                s_line_map=line[i]
                for j in range(0,len(s_line_map['points'])):
                    if s_line_map['points'][j]==key:
                        is_need_del=True
                        break
                    # else:
                    #     if s_line_map['points'][j]>key:
                    #         s_line_map['points'][j]-=1
                if is_need_del is False:
                    line_obj.append(s_line_map)
            
            sql="update images set line=%s where name=%s"
            self.db.execute(sql,(json.dumps(line_obj),name))
            self.conn.commit()
            return True
        except Exception as e:
            print(e)
            return False 

    def delete_point(self,name,key):#删除点 索引变 线不变 删除的点不在线上
        print('delete_point')
        try:
            print('def delete_point(self,name,key')
            #删除某个点
            sql="select point from images where name=%s order by name desc limit 1;"
            self.db.execute(sql,name)
            key_points=self.db.fetchone()[0]
            key_points=json.loads(key_points)
            print('key_points', key_points)
            key_points_obj=[]
            find_point_status=False
            for i in range(0,len(key_points)):
                print("当前遍历到：",key_points[i])
                if key_points[i]["key"]!=key:
                    if find_point_status:
                        key_points[i]["key"]-=1
                        key_points_obj.append(key_points[i])
                    else:
                        key_points_obj.append(key_points[i])
                else:
                    find_point_status=True
            print('name = :', name, 'key = : ', key)
            print('key_points_obj', key_points_obj)
            sql="update images set point=%s where name=%s"
            self.db.execute(sql,(json.dumps(key_points_obj),name))
            self.conn.commit()


            #曲线中点的索引联动删除 只要有这个索引 就会删除 其他的改变索引就行
            sql="select line from images where name=%s order by name desc limit 1;"
            self.db.execute(sql,name)
            line=self.db.fetchone()[0]
            line=json.loads(line)
            line_obj=[]

            for i in range(0,len(line)):
                is_need_del=False
                s_line_map=line[i]
                for j in range(0,len(s_line_map['points'])):
                    if s_line_map['points'][j]==key: #这里执行不到 line上没有这个point的key
                        is_need_del=True
                        break
                    else:
                        if s_line_map['points'][j]>key:
                            s_line_map['points'][j]-=1
                if is_need_del is False:
                    line_obj.append(s_line_map)
            print('line_obj : ', line_obj)
            sql="update images set line=%s where name=%s"
            self.db.execute(sql,(json.dumps(line_obj),name))
            self.conn.commit()
 
            return True
        except Exception as e:
            print(e)
            return False

    def select_pic(self,name):
        
        try:
            sql="select picture from images where \
            name=%s order by name desc limit 1;"
            self.db.execute(sql,(name))
            img=self.db.fetchone()[0]
            return img
        except Exception as e:
            print(e)
            return None


    '''
    value:次数
    point:[1,2,3]
    '''
    def insert_line(self,name,value,point):
        print('insert_line')
        try:
            sql="select line from images where name=%s";
            self.db.execute(sql,(name))
            res=self.db.fetchone()[0]
            if res=="":
                obj=[]
            else:
                print("res:",res)
                obj=json.loads(res)
                print(type(obj))
            print(obj)
            obj.append({'value':value,'points':point})
            print("after append:",obj)
            sql="update images set line=%s where name=%s"
            self.db.execute(sql,(json.dumps(obj),name))
            self.conn.commit()
        except Exception as e:
            print(e)
            return None
    

    '''
     [
         {"value":1,"point":[1,2,3]}
         {"value":2,"point":[1,2,3]}
         {"value":3,"point":[1,2,3]}
     ]
    '''
    def select_line(self,name):
        try:
            sql="select line from images where \
            name=%s order by name desc limit 1;"
            self.db.execute(sql,name)
            line=self.db.fetchone()[0]
            return json.loads(line)
        except Exception as e:
            print(e)
            return None

# d=DBManager()

# # d.setup()

# fp=open("test.jpg","rb")
# img = fp.read()
# fp.close()

# obj=[
#     {
#        "value":"1",
#        "point":"{1,3,4,5,6}"
#     },
#     {
#        "value":"1",
#        "point":"{1,3,4,5,6}"
#     }
# ]

# obj=[{"dim":1,"point":[0,1,3,4,5,6]},{"dim":2,"point":[1,3,4,5]}]

# point=[{1,2},{3,4},{4,5},{7,8},{9,10},{11,12},{13,14},{15,16},{17,18},{19,20}]


# point=[[1,2],[3,4]]

# d.insert("1.jpg",img,point,[])

# d.update_line("1.jpg",3,[4,5,6,7,8])

# d.delete_point("1.jpg",1)


# d.insert_point("1.jpg",[19,20])

# d.insert_line("1.jpg",5,[3,4,5])


# d.delete_point("1.jpg",2)
# print(d.update_line("1.jpg",2,[3,4,7,8,9]))


# print(d.select_line("1.jpg"))
